﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using VOL;
using DAL;

namespace BLL
{
    public class KhachHangBL
    {
        private DataAccess dataAccess;
        public KhachHangBL()
        {
            dataAccess = new DataAccess();
        }

        public DataTable GetData()
        {
            string strSql = "SELECT * FROM tbl_KhachHang ORDER BY MaKhachHang DESC ";
            return dataAccess.GetData(strSql);
        }

        public int GetMaKhachHangNew()
        {
            string strSql = "SELECT TOP (1) MaKhachHang FROM tbl_KhachHang ORDER BY MaKhachHang DESC ";
            return int.Parse(dataAccess.GetData(strSql).Rows[0]["MaKhachHang"].ToString());
        }
        //Thêm
        public bool insertKhachHang(KhachHang obj)
        {
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@HoTenKhachHang", SqlDbType.NVarChar) { Value = obj.HoTenKhachHang };
            param[1] = new SqlParameter("@DiaChi", SqlDbType.NVarChar) { Value = obj.DiaChi };
            param[2] = new SqlParameter("@MaThanhPho", SqlDbType.Int) { Value = obj.MaThanhPho };
            param[3] = new SqlParameter("@MaQuanHuyen", SqlDbType.Int) { Value = obj.MaQuanHuyen };
            param[4] = new SqlParameter("@DienThoai", SqlDbType.NVarChar) { Value = obj.DienThoai };
            param[5] = new SqlParameter("@DienThoaiDiDong", SqlDbType.NVarChar) { Value = obj.DienThoaiDiDong };
            param[6] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = obj.Email };

            string strSql = "INSERT INTO tbl_KhachHang(HoTenKhachHang,DiaChi,MaThanhPho,MaQuanHuyen,DienThoai,DienThoaiDiDong,Email ) VALUES (@HoTenKhachHang,@DiaChi,@MaThanhPho,@MaQuanHuyen,@DienThoai,@DienThoaiDiDong,@Email)";
            dataAccess.Execute(strSql, param);

            return true;
        }

        ////Xóa
        public bool deleteKhachHang(KhachHang obj)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaKhachHang", SqlDbType.Int) { Value = obj.MaKhachHang };

            string strSql = "DELETE FROM tbl_KhachHang WHERE MaKhachHang = @MaKhachHang";
            dataAccess.Execute(strSql, param);
            return true;
        }

        ////Sửa
        public bool updateKhachHang(KhachHang obj)
        {
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@HoTenKhachHang", SqlDbType.NVarChar) { Value = obj.HoTenKhachHang };
            param[1] = new SqlParameter("@DiaChi", SqlDbType.NVarChar) { Value = obj.DiaChi };
            param[2] = new SqlParameter("@MaThanhPho", SqlDbType.Int) { Value = obj.MaThanhPho };
            param[3] = new SqlParameter("@MaQuanHuyen", SqlDbType.Int) { Value = obj.MaQuanHuyen };
            param[4] = new SqlParameter("@DienThoai", SqlDbType.NVarChar) { Value = obj.DienThoai };
            param[5] = new SqlParameter("@DienThoaiDiDong", SqlDbType.NVarChar) { Value = obj.DienThoaiDiDong };
            param[6] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = obj.Email };
            param[7] = new SqlParameter("@MaKhachHang", SqlDbType.Int) { Value = obj.MaKhachHang };

            string strSql = "UPDATE tbl_KhachHang SET " +
                            "HoTenKhachHang = @HoTenKhachHang, " +
                            "DiaChi = @DiaChi, " +
                            "MaThanhPho = @MaThanhPho, " +
                            "MaQuanHuyen = @MaQuanHuyen, " +
                            "DienThoai = @DienThoai, " +
                            "DienThoaiDiDong = @DienThoaiDiDong, " +
                            "Email = @Email " +
                            "WHERE MaKhachHang = @MaKhachHang";
            dataAccess.Execute(strSql, param);
            return true;
        }

        ////Tìm theo Mã Khách Hàng
        public DataTable searchIDKhachHang(int maKhachHang)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@MaKhachHang", SqlDbType.Int) { Value = maKhachHang };

            string strSql = "SELECT * FROM tbl_KhachHang WHERE MaKhachHang = @MaKhachHang";
            return dataAccess.GetData(strSql, param);
        }
        public DataTable getTongTien(int maKhachHang)
        {
            string strSql = "SELECT        tbl_HoaDon.MaKhachHang, tbl_KhachHang.HoTenKhachHang, tbl_KhachHang.DiaChi, tbl_KhachHang.MaThanhPho, tbl_KhachHang.MaQuanHuyen,"
                            + " tbl_KhachHang.DienThoai, tbl_KhachHang.DienThoaiDiDong, tbl_KhachHang.Email, SUM(tbl_HoaDon.SoLuong * tbl_HoaDon.DonGia) AS TongTien"
                            + " FROM            tbl_HoaDon INNER JOIN"
                            + " tbl_KhachHang ON tbl_HoaDon.MaKhachHang = tbl_KhachHang.MaKhachHang"
                            + " WHERE        tbl_HoaDon.MaKhachHang = " + maKhachHang.ToString()
                            + " GROUP BY tbl_HoaDon.MaKhachHang, tbl_KhachHang.HoTenKhachHang, tbl_KhachHang.DiaChi, tbl_KhachHang.MaThanhPho, tbl_KhachHang.MaQuanHuyen, "
                            + " tbl_KhachHang.DienThoai, tbl_KhachHang.DienThoaiDiDong, tbl_KhachHang.Email";
            return dataAccess.GetData(strSql);
        }
        //Tìm theo tên khách hàng
        public DataTable searchTenKhachHang(string tenKhachHang)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@TenKhachHang", SqlDbType.NVarChar) { Value = tenKhachHang };

            string strSql = "SELECT * FROM tbl_KhachHang WHERE HoTenKhachHang like N'%"+tenKhachHang+"%'";
            return dataAccess.GetData(strSql, param);
        }
    }
}
